package com.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.dao.ProductDao;
import com.dbutil.JDBCTool;
import com.entity.Product;

public class ProductDaoimpl implements ProductDao{
	
	QueryRunner runn = new QueryRunner();
	
	//查询
	@Override
	public List query() {
		// TODO Auto-generated method stub
		Connection conn = JDBCTool.openconn();
		String sql = "select * from easybuy_product;";
		List list = null;
		try {
			list = runn.query(conn, sql, new BeanListHandler<Product>(Product.class));
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	
	
	//查询多个指定范围商品信息
	@Override
	public List querystoer(int p,int page) {
		Connection conn = JDBCTool.openconn();
		String sql = "select * from easybuy_product order by EP_STOCK limit ?,?;";
		List list = null;
		try {
			list = runn.query(conn, sql, new BeanListHandler<Product>(Product.class),p,page);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	//查询单个商品信息
	@Override
	public Product query(int id) {
		Connection conn = JDBCTool.openconn();
		String sql = "select * from easybuy_product where EP_ID=?;";
		Product p = null;
		try {
			p = runn.query(conn, sql, new BeanHandler<Product>(Product.class),id);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
	
	//查询商品类多个商品信息
	@Override
	public List querylist(int id) {
		Connection conn = JDBCTool.openconn();
		String sql = "select * from easybuy_product where EPC_CHILD_ID=?;";
		List list = null;
		try {
			list = runn.query(conn, sql, new BeanListHandler<Product>(Product.class),id);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	//分页查询
	@Override
	public List pagequery(int p,int page) {
		Connection conn = JDBCTool.openconn();
		int n=(p-1)*page;
		String sql = "select * from easybuy_product limit ?,?;";
		List list = null;
		try {
			list = runn.query(conn, sql, new BeanListHandler<Product>(Product.class),n,page);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	//商品类父id分页查询
	@Override
	public List fquery(int p,int id,int page) {
		Connection conn = JDBCTool.openconn();
		int n=(p-1)*page;
		String sql = "select * from easybuy_product where EPC_CHILD_ID=? limit ?,?;";
		List list = null;
		try {
			list = runn.query(conn, sql, new BeanListHandler<Product>(Product.class),id,n,page);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
		
	
	//添加
	@Override
	public int add(Object... param) {
		Connection conn = JDBCTool.openconn();
		String sql = "insert into easybuy_product  " + 
				"(EP_NAME,EP_DESCRIPTION,EP_PRICE,EP_STOCK,EPC_CHILD_ID,EP_FILE_NAME)  " + 
				"value(?,?,?,?,?,?);";
		long l = 0;
		try {
			l = runn.update(conn, sql, param);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (int)l;
	}
	
	//修改
	@Override
	public int update(Object... param) {
		Connection conn = JDBCTool.openconn();
		String sql = "update easybuy_product set  " + 
				"EP_NAME=?,EP_DESCRIPTION=?,EP_PRICE=?,EP_STOCK=?,EPC_CHILD_ID=?,EP_FILE_NAME=?   " + 
				"where EP_ID=?; ";
		long l = 0;
		try {
			l = runn.update(conn, sql,param);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (int)l;
	}
	
	//删除
	@Override
	public int del(int id) {
		Connection conn = JDBCTool.openconn();
		String sql = "delete from easybuy_product where EP_ID=?;";
		long l = 0;
		try {
			l = runn.update(conn, sql, id);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (int)l;
	}
	
	//统计
	@Override
	public int count() {
		String seco="select count(*) from easybuy_product";
		Connection conn=JDBCTool.openconn();
		long lo=0;
		try {
			lo = runn.query(conn,seco,new ScalarHandler<Long>());
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (int)lo;
	}
	
	//id统计
	@Override
	public int count(int id) {
		String seco="select count(*) from easybuy_product where EPC_CHILD_ID=?";
		Connection conn=JDBCTool.openconn();
		long lo=0;
		try {
			lo = runn.query(conn,seco,new ScalarHandler<Long>(),id);
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return (int)lo;
	}


	//今日特价
	@Override
	public List Sale() {
		// TODO Auto-generated method stub
		String sa="select * from easybuy_product order by EP_PRICE limit 0,8";
		Connection conn=JDBCTool.openconn();
		List list=null;
		try {
			list = runn.query(conn, sa, new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
